import pymysql
from random import Random

def random_str(randomlength=20):
    str = ''
    chars = 'AaBbCcDdEeFfGgHhIiJjKkLlMmNnOoPpQqRrSsTtUuVvWwXxYyZz0123456789'
    length = len( chars ) - 1
    random = Random()
    for i in range( randomlength ):
        if i % 4 == 0 and i != 0:
            str += "-"
        else:
            str += chars[random.randint( 0, length )]

    return str


if __name__ == '__main__':
    # 获取mysql db 驱动
    db = pymysql.connect( "192.168.0.242", "root", "Kc@123456", "helmet_operate_sit" )
    cursor = db.cursor()
    sql = "SELECT oi.contract_name,ci.country_id,oi.business_type FROM kc_order_info oi LEFT JOIN kc_customer_info ci ON oi.order_id = ci.order_id GROUP BY contract_name"
    cursor.execute( sql )
    # 获取所有记录列表
    results = cursor.fetchall()

    for result in results:
        name = result[0]
        regionId = result[1]
        type = result[2]
        if name != '' and name != '/' and name != None and name != "JACKY'S ELECTRONICS (S) PTE LTD ":
            sql1 = "SELECT count(*) FROM kc_agent_info WHERE agent_name = \'" + name + "\'"
            cursor.execute( sql1 )
            fetchall = cursor.fetchall()
            count_ = fetchall[0][0]
            if count_ == 0:
                s = random_str()
                if type == '8090ff06-ac8c-4d6d-8e34-22ebe9dd27c3':
                    insertSql = 'INSERT INTO kc_agent_info (id, agent_number, country, agent_name, agent_e_name, authorization_time,authorized_batch, agent_price, ai_agent_price, watch_price, order_quantity, industry,level, remarks, creat_time, detect_flag, type)VALUES(UUID(),\''+s+'\',\'' + regionId + '\',\'' + name + '\',\'\',\'2020-12-30\',\'\',0.0,0.0,0.0,0,\'\',\'a78b3827-cf9f-4add-8008-2453eec062a1\',\'\',NOW(),\'0\',\'1\')'
                    print( insertSql )
                    cursor.execute( insertSql )
                    db.commit()
                else:
                    insertSql = 'INSERT INTO kc_agent_info (id, agent_number, country, agent_name, agent_e_name, authorization_time,authorized_batch, agent_price, ai_agent_price, watch_price, order_quantity, industry,level, remarks, creat_time, detect_flag, type)VALUES(UUID(),\''+s+'\',\'' + regionId + '\',\'' + name + '\',\'\',\'2020-12-30\',\'\',0.0,0.0,0.0,0,\'\',\'a78b3827-cf9f-4add-8008-2453eec062a1\',\'\',NOW(),\'0\',\'0\')'
                    print( insertSql )
                    cursor.execute( insertSql )
                    db.commit()
